/*
 * 创建日期 2009-11-27
 *
 * 成都天和软件公司
 * 电话：028-85425861
 * 传真：028-85425861-8008
 * 邮编：610041 
 * 版权所有
 */
package  org.fantasy.common.db.oracle;

import java.io.ByteArrayInputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.Predicate;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;

import  org.fantasy.common.db.AbstractQuery;
import  org.fantasy.common.db.bean.Cell;
import  org.fantasy.common.db.bean.ReportRow;
import  org.fantasy.common.db.bean.Row;
import  org.fantasy.common.db.util.EntityUtil;
import  org.fantasy.common.db.util.ResultSetRow;
import  org.fantasy.common.db.util.SqlUtil;
import  org.fantasy.common.exception.DBException;

/**
 *  Oracle查询、修改、删除、新增
 * @author: 王文成
 * @version: 1.0
 * @since 2009-11-27
 */
public class OracleQuery extends AbstractQuery {

    private static final String FORMAT_LIST = "FORMAT_LIST";

    private static final String FORMAT_REPORT = "FORMAT_REPORT";

    private static final String FORMAT_XML = "FORMAT_XML";

    private static Log log = LogFactory.getLog(OracleQuery.class);

    @SuppressWarnings("all")
    public int count() throws DBException {
        String countSql = OracleUtils.getCountSql(sql,countType);
        String sql = prepare(countSql);
        List<Row> rows = (List<Row>) executeQuery(sql, FORMAT_LIST);
        Cell cell = rows.get(0).getCell(OracleUtils.COUNT);
        return cell.getInt();
    }

    public List<? extends Object> list(Class cls) throws DBException {
        List<Row> rows = list();
        List<Object> objList = new ArrayList<Object>();

        for (Row row : rows) {
            Object obj = convertRow(row, cls);
            objList.add(obj);
        }
        return objList;
    }
    
    @SuppressWarnings("all")
    private Object convertRow(Row row, Class cls) throws DBException {
        try {
            Object obj = cls.newInstance();
            Set<Map.Entry<Object, Cell>> s = (Set<Map.Entry<Object, Cell>>) row.entrySet();
            for (Map.Entry<Object, Cell> e : s) {
                String property = e.getKey().toString();
                Object value = e.getValue().getValue();
                boolean b = EntityUtil.setObjectProperty(obj, property, value);
            }
            return obj;
        } catch (Exception e) {
            log.debug(e);
            throw new DBException(e);
        }
    }

    @SuppressWarnings("all")
    public Row firstRow() throws DBException {
        String qrySql = OracleUtils.getPagedSql(sql, 1, 1);
        String sql = prepare(qrySql);
        List<Row> rows = (List<Row>) executeQuery(sql, FORMAT_LIST);
        if (!rows.isEmpty())
            return rows.get(0);
        return null;
    }

    @SuppressWarnings("all")
    public List<Row> list() throws DBException {
        String qrySql = (isPaged() ? OracleUtils.getPagedSql(sql, pageStart, pageSize) : sql);
        String sql = prepare(qrySql);
        return (List<Row>) executeQuery(sql, FORMAT_LIST);
    }

    @SuppressWarnings("all")
    public List<ReportRow> report() throws DBException {
        String qrySql = (isPaged() ? OracleUtils.getPagedSql(sql, pageStart, pageSize) : sql);
        String sql = prepare(qrySql);
        return (List<ReportRow>) executeQuery(sql, FORMAT_LIST);
    }

    @SuppressWarnings("all")
    public Document xml() throws DBException {
        String qrySql = (isPaged() ? OracleUtils.getPagedSql(sql, pageStart, pageSize) : sql);
        String sql = prepare(qrySql);
        return (Document) executeQuery(sql, FORMAT_XML);
    }

    /**
     * 执行查询
     * 
     * @param qrySql
     * @return
     * @throws DBException
     */
    private Object executeQuery(String sql, String type) throws DBException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
        	conn = dataSource.getConnection();
			//mod by liux at 20100119 修改取得connection的方式，DataSourceUtils可以取得事务中的连接
			//conn=DataSourceUtils.getConnection(dataSource);
            stmt = conn.prepareStatement(sql);
            stmt.setQueryTimeout(options.getTimeOut());
            log.debug("Set query time out:" + options.getTimeOut());
            log.debug(OracleUtils.getRuntimeSql(sql, values));
            for (int i = 0; i < values.size(); i++) {
                if (values.get(i) instanceof Integer)
                    stmt.setInt(i + 1, ((Integer) values.get(i)).intValue());
                else if (values.get(i) instanceof Long)
                    stmt.setLong(i + 1, ((Long) values.get(i)).longValue());
                else if (values.get(i) instanceof String)
                    stmt.setString(i + 1, values.get(i).toString());
                else if (values.get(i) instanceof java.sql.Date)
                    stmt.setDate(i + 1, (java.sql.Date) values.get(i));
                else if (values.get(i) instanceof java.sql.Timestamp)
                    stmt.setTimestamp(i + 1, (java.sql.Timestamp) values.get(i));
                else if (values.get(i) instanceof java.util.Date)
                    stmt.setDate(i + 1, new java.sql.Date(((java.util.Date) values.get(i)).getTime()));
                else if (values.get(i) instanceof Double)
                    stmt.setDouble(i + 1, ((Double) values.get(i)).doubleValue());
                else if (values.get(i) instanceof Float)
                    stmt.setFloat(i + 1, ((Float) values.get(i)).floatValue());
                else
                    stmt.setObject(i + 1, values.get(i));
            }
            rs = stmt.executeQuery();
            if (FORMAT_REPORT.equals(type))
                return new ResultSetRow(rs).getRows();
            else if (FORMAT_LIST.equals(type))
                return new ResultSetRow(rs).getReport();
            else if (FORMAT_XML.equals(type))
                return new ResultSetRow(rs).getXml();
            else
                throw new DBException("无效的数据类型:[" + type + "]");
        } catch (Exception ex) {
            ex.printStackTrace();
            log.error(ex);
            if (ex.getMessage().indexOf("ORA-01013") > -1) {
                throw new DBException("数据库查询超时！", OracleUtils.getRuntimeSql(sql, values));
            }
            throw new DBException(ex.getMessage(), OracleUtils.getRuntimeSql(sql, values));
        } finally {
            OracleUtils.closeAll(conn, stmt, rs);
        }
    }

    public int update() throws DBException {
        String sql = prepare(this.sql);
        return executeUpdate(sql);
    }

    /**
     * DML
     * 
     * @param sql
     * @return
     * @throws DBException
     */
    private int executeUpdate(String sql) throws DBException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
        	conn = dataSource.getConnection();
			//mod by liux at 20100119 修改取得connection的方式，DataSourceUtils可以取得事务中的连接
			//conn=DataSourceUtils.getConnection(dataSource);
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement(sql);
            log.debug(OracleUtils.getRuntimeSql(sql, values));
            for (int i = 0; i < values.size(); i++) {
                if (values.get(i) instanceof Integer)
                    stmt.setInt(i + 1, ((Integer) values.get(i)).intValue());
                else if (values.get(i) instanceof Long)
                    stmt.setLong(i + 1, ((Long) values.get(i)).longValue());
                else if (values.get(i) instanceof String) {
                    String s = (String) values.get(i);
                    if (s.getBytes().length < 4000)
                        stmt.setString(i + 1, values.get(i).toString());
                    else {
                        if (s.getBytes().length > 4000)
                            stmt.setCharacterStream(i + 1, new InputStreamReader(new ByteArrayInputStream((new String(s
                                    .getBytes(), 0, 4000)).getBytes())), 4000);
                        else
                            stmt.setCharacterStream(i + 1,
                                    new InputStreamReader(new ByteArrayInputStream(s.getBytes())), s.length());
                    }
                } else if (values.get(i) instanceof java.sql.Date)
                    stmt.setDate(i + 1, (java.sql.Date) values.get(i));
                else if (values.get(i) instanceof java.sql.Timestamp)
                    stmt.setTimestamp(i + 1, (java.sql.Timestamp) values.get(i));
                else if (values.get(i) instanceof java.util.Date)
                    stmt.setDate(i + 1, new java.sql.Date(((java.util.Date) values.get(i)).getTime()));
                else if (values.get(i) instanceof Double)
                    stmt.setDouble(i + 1, ((Double) values.get(i)).doubleValue());
                else if (values.get(i) instanceof Float)
                    stmt.setFloat(i + 1, ((Float) values.get(i)).floatValue());
                else if (values.get(i) instanceof BigDecimal)
                    stmt.setBigDecimal(i + 1, (BigDecimal) values.get(i));
                else
                    stmt.setObject(i + 1, values.get(i));
            }
            int count = stmt.executeUpdate();
            conn.commit();
            return count;
        } catch (SQLException ex) {
            log.error(ex);
            throw new DBException(ex.getMessage(), OracleUtils.getRuntimeSql(sql, values));
        } finally {
            OracleUtils.closeAll(conn, stmt, null);
        }
    }
    
    @Override
    public String prepare(String sql){
        List<String>  params = SqlUtil.getParameterList(sql);
        List<Object> paramValues = new ArrayList<Object>();
        if( !paramMap.isEmpty() ){
            // 过滤掉"MI","SS"
            CollectionUtils.filter(params,new Predicate(){
                public boolean evaluate(Object object) {
                    String o = (String)object;
                    return !("MI".equalsIgnoreCase(o) || "SS".equalsIgnoreCase(o));
                }
            });
            for (String param : params) {
                paramValues.add(paramMap.get(param));
                sql = StringUtils.replaceOnce(sql, ":" + param, "?");
            }
        }
        this.values = paramValues;
        return sql;
    }
    
    /**
    * 取得运行时SQL
    * @return
     */
    public String getRuntimeSql() {
        String sql = prepare(this.sql);
        return OracleUtils.getRuntimeSql(sql, values);
    }
    
    public static void main(String[] args) {
        String sql = "select to_char('24H:MI:SS') from cust";
        List<String>  params = SqlUtil.getParameterList(sql);
        List<Object> paramValues = new ArrayList<Object>();
        Map paramMap = new HashMap();
        if( !paramMap.isEmpty() ){
            // 过滤掉"MI","SS"
            CollectionUtils.filter(params,new Predicate(){
                public boolean evaluate(Object object) {
                    String o = (String)object;
                    return !("MI".equalsIgnoreCase(o) || "SS".equalsIgnoreCase(o));
                }
            });
            for (String param : params) {
                paramValues.add(paramMap.get(param));
                sql = StringUtils.replaceOnce(sql, ":" + param, "?");
            }
        }
        System.out.println(sql);
    }
}
